package nus.iss.laps.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import nus.iss.laps.cri.Criteria;
import nus.iss.laps.model.Leavesentitlement;

public class LeavesentitlementDAO extends DAO implements ILeavesentitlementDAO, RowMapper<Leavesentitlement> {

	public LeavesentitlementDAO()	{
		table = "leavesentitlement";
	}
	
	@Override
	public boolean addLeavesentitlement(Leavesentitlement leavesentitlement)
			throws SQLException {
			query = "insert into " + table + "(annualLeave, compensationLeave, medicalLeave, designation) " 
					+ " values( ?, ?, ?, ?)";
			jdbcTemplate = new JdbcTemplate(dataSource);
			return jdbcTemplate.update(query, leavesentitlement.getAnnualLeave(), leavesentitlement.getCompensationLeave(), leavesentitlement.getMedicalLeave(), leavesentitlement.getDesignation()) > 0;
	}

	@Override
	public boolean updateLeavesentitlement(Leavesentitlement leavesentitlement)
			throws SQLException {
		query =  "update " + table + " set "
				+ "annualLeave = ?, "
				+ "compensationLeave = ?, "
				+ "medicalLeave = ?, "
				+ "designation = ? "
				+"where entitlementID = '" + leavesentitlement.getEntitlementID() + "'";
		jdbcTemplate = new JdbcTemplate(dataSource);
		return jdbcTemplate.update(query,leavesentitlement.getAnnualLeave(),leavesentitlement.getCompensationLeave(),
				leavesentitlement.getMedicalLeave(),leavesentitlement.getDesignation()) > 0;
		}

	@Override
	public boolean deleteLeavesentitlement(int entitlementID) throws SQLException {
		query = getDeleteQuery() + " where entitlementID = '" + entitlementID + "'";
		jdbcTemplate = new JdbcTemplate(dataSource);
		return jdbcTemplate.update(query) > 0;
	}
	
	public boolean deleteLeavesentitlement(String designation) throws SQLException {
		query = getDeleteQuery() + " where designation = '" + designation + "'";
		jdbcTemplate = new JdbcTemplate(dataSource);
		return jdbcTemplate.update(query) > 0;
	}

	@Override
	public Leavesentitlement getLeavesentitlement(int entitlementID)
			throws SQLException {
		query = getSelectQuery() + " where entitlementID = '" + entitlementID + "'";
		try {
			jdbcTemplate = new JdbcTemplate(dataSource);
			return jdbcTemplate.queryForObject(query, this);
		} catch (IncorrectResultSizeDataAccessException e) {
			return null;
		}
	}
	
	public boolean isExisted(String designation)	throws SQLException{
		query = getSelectQuery() + " where designation = '" + designation + "'";
		try {
			jdbcTemplate = new JdbcTemplate(dataSource);
			return (jdbcTemplate.queryForObject(query, this)).getEntitlementID() != 0;
		} catch (IncorrectResultSizeDataAccessException e) {
			return true;
		}
	}

	@Override
	public List<Leavesentitlement> getAllLeavesentitlement()
			throws SQLException {
		query = getSelectQuery();
		jdbcTemplate = new JdbcTemplate(dataSource);
		return jdbcTemplate.query(query, this);
	}

	@Override
	public Leavesentitlement mapRow(ResultSet arg0, int arg1)
			throws SQLException {
		Leavesentitlement l_leavesentitlement = new Leavesentitlement();
		l_leavesentitlement.setEntitlementID(arg0.getInt("entitlementID"));
		l_leavesentitlement.setAnnualLeave(arg0.getInt("annualLeave"));
		l_leavesentitlement.setCompensationLeave(arg0.getInt("compensationLeave"));
		l_leavesentitlement.setMedicalLeave(arg0.getInt("medicalLeave"));
		l_leavesentitlement.setDesignation(arg0.getString("designation"));
	      return l_leavesentitlement;
	}
	
	public List<Leavesentitlement> getLeavesentitlement(Criteria cri) throws SQLException{
		String l_cri = " (entitlementID like '%" + cri.getKeyword() + "%'"
				+ " or designation like '%" + cri.getKeyword() + "%')";

		query = getSelectQuery()
				+ " where " + l_cri 
				+ " order by entitlementID"
				+ " limit " + cri.getRowIndex() + ", " + cri.getRowLimit();
		jdbcTemplate = new JdbcTemplate(dataSource);
		
		String l_queryCount = "select count(entitlementID) from " + table + " where " + l_cri;
		cri.setRecordCount(jdbcTemplate.queryForLong(l_queryCount));
		
		return jdbcTemplate.query(query, this);
}
}